package com.example.zc.db;

import android.annotation.SuppressLint;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;

import com.example.zc.Bean.CityBean;

import java.util.ArrayList;
import java.util.List;

/*
 * 数据库操作类
 * */
public class MyDbService {

    private final MyDbHelper myDbHelper;
    public static final String table = "anniversary";
    public MyDbService(Context context) {
        myDbHelper = new MyDbHelper(context);
    }

    public void insertPerson(ContentValues value) {
        SQLiteDatabase writableDatabase = getDb();
        writableDatabase.insertOrThrow("User", null, value);
    }

    @SuppressLint("Range")
    public String login(String name) {
        SQLiteDatabase writableDatabase = getDb();

        String res = null;
        Cursor cursor = writableDatabase.rawQuery("select * from User where username = ? ", new String[]{name}, null);
        if (cursor.moveToFirst()) {
            res = cursor.getString(cursor.getColumnIndex("password"));
        }
        cursor.close();
        return res;
    }

    public SQLiteDatabase getDb() {
        return myDbHelper.getWritableDatabase();
    }

    @SuppressLint("Range")
    public ArrayList<User> getPersonList() {
        SQLiteDatabase writableDatabase = getDb();
        ArrayList<User> list = new ArrayList<>();
        Cursor cursor = writableDatabase.rawQuery("select * from User", null);

        if (cursor.moveToFirst()) {
            do {
                User user = new User();
                String name = cursor.getString(cursor.getColumnIndex("username"));
                String password = cursor.getString(cursor.getColumnIndex("password"));
                String _id = cursor.getString(cursor.getColumnIndex("_id"));
                int age = cursor.getInt(cursor.getColumnIndex("age"));
                user.setName(name);
                user.setPassword(password);
                user.setId(_id);
                user.setAge(age);
                list.add(user);
            } while (cursor.moveToNext());
        }
        cursor.close();
        return list;
    }

    public void delete(String username) {
        SQLiteDatabase writableDatabase = getDb();
        writableDatabase.delete("User", "username = ?", new String[]{username});
    }

    public int update(ContentValues values) {
        SQLiteDatabase writableDatabase = getDb();
        return writableDatabase.update("User", values, " username = ?", new String[]{String.valueOf(values.get("username"))});
    }

    public void insert(String head, String name, String number, String password, String address, String sex, int age) {
        SQLiteDatabase db = getDb();
        ContentValues values = new ContentValues();
        values.put("head", head);
        values.put("name", name);
        values.put("number", number);
        values.put("password", password);
        values.put("address", address);
        values.put("sex", sex);
        values.put("age", age);
        db.insert("tb_friend", null, values);
        db.close();
    }

    //查询所有城市记录
    public List<CityBean> select() {
        List<CityBean> list = new ArrayList<>();
        SQLiteDatabase db = getDb();
        String sql = "select * from tb_city";
        Cursor cursor = db.rawQuery(sql, null);
        while (cursor.moveToNext()) {
            CityBean cityBean = new CityBean();
            cityBean.setCityId(cursor.getString(0));
            cityBean.setCityName(cursor.getString(1));
            cityBean.setCitypinyin(cursor.getString(2));
            list.add(cityBean);
        }
        cursor.close();
        db.close();
        return list;
    }

    //通过拼音查询城市记录
    public List<CityBean> select(String pinyin) {
        List<CityBean> list = new ArrayList<>();
        try {
            SQLiteDatabase db = getDb();
            String sql = "select * from tb_city where pinyin like '%" + pinyin + "%' or id like '%" + pinyin + "%' or name like '%" + pinyin + "%' ";
            Cursor cursor = db.rawQuery(sql, null);
            while (cursor.moveToNext()) {
                CityBean cityBean = new CityBean();
                cityBean.setCityId(cursor.getString(0));
                cityBean.setCityName(cursor.getString(1));
                cityBean.setCitypinyin(cursor.getString(2));
                list.add(cityBean);
            }
            cursor.close();
            db.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }

    //通过拼音查询城市记录
    public List<CityBean> selectMyCity() {
        List<CityBean> list = new ArrayList<>();
        SQLiteDatabase db = getDb();
        String sql = "select * from tb_city where mark = 1";
        Cursor cursor = db.rawQuery(sql, null);
        while (cursor.moveToNext()) {
            CityBean cityBean = new CityBean();
            cityBean.setCityId(cursor.getString(0));
            cityBean.setCityName(cursor.getString(1));
            cityBean.setCitypinyin(cursor.getString(2));
            list.add(cityBean);
        }
        cursor.close();
        db.close();
        return list;
    }

    public void saveMyCity(String cityid) {
        SQLiteDatabase db = getDb();
        String sql = "update tb_city set mark = 1 where id = '" + cityid + "'";
        db.execSQL(sql);
        db.close();
    }

    public void deleteMyCity(String cityid) {
        SQLiteDatabase db = getDb();
        String sql = "update tb_city set mark = 0 where id = '" + cityid + "'";
        db.execSQL(sql);
        db.close();
    }
    //插入日程
    public long insertAuto(CalendarEntity calendarEntity) throws SQLException {
        ContentValues value = new ContentValues();
        value.put(CalendarEntity.DESCRIPTION, calendarEntity.getDescription());
        value.put(CalendarEntity.TIME, calendarEntity.getTime());
        return getDb().insertOrThrow(table, null, value);
    }
    //插入日程
    public long insert(CalendarEntity calendarEntity) throws SQLException {
        ContentValues value = new ContentValues();
        value.put(CalendarEntity.DESCRIPTION, calendarEntity.getDescription());
        value.put(CalendarEntity.TIME, calendarEntity.getTime());
        value.put("flag", "2");
        return getDb().insertOrThrow(table, null, value);
    }

    //通过日期查询日程
    @SuppressLint("Range")
    public CalendarEntity getInfoByData(String s) {
        CalendarEntity calendarEntity = null;
        Cursor cursor = getDb().rawQuery("select * from " + table + " where id=?", new String[]{s});
        if (cursor.getCount() > 0) {
            cursor.moveToFirst();
            calendarEntity = new CalendarEntity();
            calendarEntity.setDescription(cursor.getString(cursor.getColumnIndex(CalendarEntity.DESCRIPTION)));
            calendarEntity.setTime(cursor.getString(cursor.getColumnIndex(CalendarEntity.TIME)));
            calendarEntity.setId(cursor.getString(cursor.getColumnIndex("id")));
            cursor.close();
        }
        return calendarEntity;
    }

    //查询全部日程
    @SuppressLint("Range")
    public List<CalendarEntity> getInfoList(int flag) {
        List<CalendarEntity> entityList = new ArrayList<>();
        Cursor cursor = null;
        //全部
        if (flag == 1) {
            cursor = getDb().rawQuery("select * from " + table, null);
        } else {
            //已完成
            //未完成
            cursor = getDb().rawQuery("select * from " + table+" where flag = ?", new String[]{flag+""});
        }

        while (cursor != null && cursor.moveToNext()) {
            CalendarEntity calendarEntity = new CalendarEntity();
            calendarEntity.setDescription(cursor.getString(cursor.getColumnIndex(CalendarEntity.DESCRIPTION)));
            calendarEntity.setTime(cursor.getString(cursor.getColumnIndex(CalendarEntity.TIME)));
            calendarEntity.setId(cursor.getString(cursor.getColumnIndex("id")));
            entityList.add(calendarEntity);
        }

        return entityList;
    }
    public long updateAuto(CalendarEntity calendarEntity) {
        ContentValues value = new ContentValues();
        value.put(CalendarEntity.DESCRIPTION, calendarEntity.getDescription());
        value.put(CalendarEntity.TIME, calendarEntity.getTime());
        value.put("flag", "3");
        return getDb().updateWithOnConflict(table, value, "id = ?", new String[]{calendarEntity.getId()}, 5);
    }
    public long update(CalendarEntity calendarEntity) {
        ContentValues value = new ContentValues();
        value.put(CalendarEntity.DESCRIPTION, calendarEntity.getDescription());
        value.put(CalendarEntity.TIME, calendarEntity.getTime());
        value.put("flag", "2");
        return getDb().updateWithOnConflict(table, value, "id = ?", new String[]{calendarEntity.getId()}, 5);
    }

    public long delete(CalendarEntity calendarEntity) {
        return getDb().delete(table, "id = ?", new String[]{calendarEntity.getId()});
    }

}
